﻿using System;
using System.Web.Services;
using System.Data.SqlClient;

public partial class CarCallBacks : PageX
{
    public SqlConnection OpenDB()
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString =
            "Data Source=.\\SQLEXPRESS;" +
            "AttachDbFilename=|DataDirectory|db1.mdf;" +
            "Integrated Security=True;User Instance=True";

        try
        {
            conn.Open();
            return conn;
        }
        catch (Exception ex)
        {
            return null;
        }

    }

    // 
    // Каталог автомобилей
    //
    public void GetCarCatalogue()
    {
        int page = Convert.ToInt32( _REQUEST["page"] );
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

// Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "car_id";
        sord = (sord == "desc" || sord == "DESC") ? sord : "ASC";

        switch (sidx)
        {
            case "vend_id":     sidx = "cars.vend_id"; break;
            case "vend_name":   sidx = "vdic.vend_name"; break;
            case "model_id":    sidx = "cars.model_id"; break;
            case "model_name":  sidx = "mdic.model_name"; break;
            case "color_id":    sidx = "cars.color_id"; break;
            case "color_name":  sidx = "cdic.color_name"; break;
            case "engine":      sidx = "cars.engine"; break;
            case "price":       sidx = "price.price"; break;
            case "comment":     sidx = "cars.comment"; break;
            default: sidx = "cars.car_id"; break;
        }
                
// Создаем структуру для возврата
        jqGridData r = new jqGridData();

// Фильтр
        string whereclaus = "";
        bool concat = false;
        if (search)
        {
            string scar_id = _REQUEST["car_id"];
            if (scar_id != null && scar_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.car_id=" + quote(scar_id);
                concat = true;
            }
            string svend_id = _REQUEST["vend_id"];
            if (svend_id != null && svend_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.vend_id=" + quote(svend_id);
                concat = true;
            }
            string svend_name = _REQUEST["vend_name"];
            if (svend_name != null && svend_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "vdic.vend_name LIKE " + quote("%"+svend_name+"%");
                concat = true;
            }
            string smodel_id = _REQUEST["model_id"];
            if (smodel_id != null && smodel_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.model_id=" + quote(smodel_id);
                concat = true;
            }
            string smodel_name = _REQUEST["model_name"];
            if (smodel_name != null && smodel_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "mdic.model_name LIKE " + quote("%"+smodel_name+"%");
                concat = true;
            }
            string scolor_id = _REQUEST["color_id"];
            if (scolor_id != null && scolor_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.color_id=" + quote(scolor_id);
                concat = true;
            }
            string scolor_name = _REQUEST["color_name"];
            if (scolor_name != null && scolor_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "cdic.color_name LIKE " + quote("%"+scolor_name+"%");
                concat = true;
            }
            string sengine = _REQUEST["engine"];
            if (sengine != null && sengine != "")
            {
                sengine = sengine.Replace(',', '.');
                whereclaus += (concat ? " AND " : "") + "LTRIM(STR(cars.engine, 6, 2)) LIKE " + quote(sengine + "%");
                concat = true;
            }
            string scomment = _REQUEST["comment"];
            if (scomment != null && scomment != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.comment LIKE " + quote("%" + scomment + "%");
                concat = true;
            }
        }
        if( whereclaus != ""  ) whereclaus = " WHERE "+whereclaus;

// Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            echo(json_encode(r));
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

// Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) AS count FROM cars "+
            "INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id "+
            "INNER JOIN models AS mdic ON mdic.model_id = cars.model_id "+
            "INNER JOIN colors AS cdic ON cdic.color_id = cars.color_id " +
            "LEFT JOIN price ON price.car_id = cars.car_id AND price.active='1' " +
            whereclaus+";";

        Int32 count = (Int32)comm.ExecuteScalar();

// Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

// Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

// Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit*page - limit + 1;
        int end_row = start_row + limit -1;

        if (end_row > count) end_row = count;

//SELECT * FROM ( SELECT        ROW_NUMBER() OVER (ORDER BY color_name DESC)  AS row_num, *
//FROM colors WHERE color_code != '00FF00' ) as sub WHERE row_num BETWEEN  2 AND 3

        string sub = "SELECT ROW_NUMBER() OVER (ORDER BY " + sidx + " " + sord + ") AS RowNum, " +
                "cars.car_id  AS car_id, cars.vend_id AS vend_id, vdic.vend_name AS vend_name, " +
                "cars.model_id AS model_id, mdic.model_name AS model_name, " +
                "cars.color_id AS color_id, cdic.color_name AS color_name, " +
                "cars.engine AS engine, price.price AS price, cars.comment AS comment FROM cars " +
                "INNER JOIN vendors AS vdic ON vdic.vend_id=cars.vend_id " +
                "INNER JOIN models  AS mdic ON mdic.model_id=cars.model_id " +
                "INNER JOIN colors  AS cdic ON cdic.color_id=cars.color_id " +
                "LEFT JOIN price ON price.car_id = cars.car_id AND price.active='1' " +
                whereclaus;
        /*
        SELECT * 
        FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY model_name ASC) AS RowNum, 
        cars.car_id  AS car_id, cars.vend_id AS vend_id, vdic.vend_name AS vend_name, 
        cars.model_id AS model_id, mdic.model_name AS model_name, 
        cars.color_id AS color_id, cdic.color_name AS color_name, 
        cars.engine AS engine FROM cars
        LEFT JOIN vendors AS vdic ON vdic.vend_id=cars.vend_id 
        LEFT JOIN models  AS mdic ON mdic.model_id=cars.model_id
        LEFT JOIN colors  AS cdic ON cdic.color_id=cars.color_id   
        )  as sub WHERE sub.RowNum BETWEEN 1 AND 5;       
         */

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub " +
            "WHERE sub.RowNum BETWEEN " + start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row-start_row+1];

        int icar_id = reader.GetOrdinal("car_id");
        int ivend_id    = reader.GetOrdinal("vend_id");
        int ivend_name  = reader.GetOrdinal("vend_name");
        int imodel_id   = reader.GetOrdinal("model_id");
        int imodel_name = reader.GetOrdinal("model_name");
        int icolor_id   = reader.GetOrdinal("color_id");
        int icolor_name = reader.GetOrdinal("color_name");
        int iengine = reader.GetOrdinal("engine");
        int iprice = reader.GetOrdinal("price");
        int icomment = reader.GetOrdinal("comment");

        int i = 0;
        while (reader.Read())
        {
            int car_id = reader.GetInt32(icar_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = car_id;
            r.rows[i].cell = new string[10];
            r.rows[i].cell[0] = car_id.ToString();
            r.rows[i].cell[1] = reader.GetInt32(ivend_id).ToString();
            r.rows[i].cell[2] = reader.GetString(ivend_name);
            r.rows[i].cell[3] = reader.GetInt32(imodel_id).ToString();
            r.rows[i].cell[4] = reader.GetString(imodel_name);
            r.rows[i].cell[5] = reader.GetInt32(icolor_id).ToString();
            r.rows[i].cell[6] = reader.GetString(icolor_name);
            r.rows[i].cell[7] = reader.GetFloat(iengine).ToString();

            if (reader.IsDBNull(iprice))
                r.rows[i].cell[8] = "н/д";
            else
                r.rows[i].cell[8] = reader.GetFloat(iprice).ToString();

            r.rows[i].cell[9] = reader.GetString(icomment);

            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(json_encode(r));
    }

    public void AddToCarCatalogue()
    {
        string car_id = _REQUEST["id"];
        string vend_id = _REQUEST["vend_id"];
        string model_id = _REQUEST["model_id"];
        string color_id = _REQUEST["color_id"];
        string engine = _REQUEST["engine"].Replace(',', '.');
        string comment = _REQUEST["comment"];

        if (car_id != "_empty" || !str_IsUInt32(vend_id) || 
            !str_IsUInt32(model_id) || !str_IsUInt32(color_id) ||
            !str_IsReal(engine) )
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем существование
            comm.CommandText = "SELECT COUNT(*) FROM cars WHERE ( " +
                "vend_id=" + quote(vend_id) + " AND model_id=" + quote(model_id) + 
                " AND color_id=" + quote(color_id) + " AND engine="+quote(engine)+" );";

            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "В каталоге уже присутствует автомобиль с указанными параметрами.");
                break;
            }

            comm.CommandText = "INSERT INTO cars (vend_id, model_id, color_id, engine, comment) VALUES (" +
                quote(vend_id) + "," + quote(model_id) + "," + quote(color_id) + "," + quote(engine)+ "," +
                quote(comment) + ");";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка добавления новой записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void EditCarCatalogue()
    {
        string car_id   = _REQUEST["id"];
        string vend_id  = _REQUEST["vend_id"];
        string model_id = _REQUEST["model_id"];
        string color_id = _REQUEST["color_id"];
        string engine   = _REQUEST["engine"].Replace(',','.');
        string comment  = _REQUEST["comment"];

        if (!str_IsUInt32(car_id) || !str_IsUInt32(vend_id) ||
            !str_IsUInt32(model_id) || !str_IsUInt32(color_id) ||
            !str_IsReal(engine))
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM cars WHERE ( " +
                "vend_id=" + quote(vend_id) + " AND model_id=" + quote(model_id) +
                " AND color_id=" + quote(color_id) + " AND engine=" + quote(engine) + 
                " AND car_id != " + quote(car_id) + " );";

            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "В справочнике уже присутствует автомобиль с указанными параметрами.");
                break;
            }

            comm.CommandText = "UPDATE cars SET " +
                "vend_id="  + quote(vend_id)  + "," +
                "model_id=" + quote(model_id) + "," +
                "color_id=" + quote(color_id) + "," +
                "engine="   + quote(engine)   + "," +
                "comment="  + quote(comment)  + " " +
               "WHERE car_id=" + quote(car_id) + ";";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка обновления записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }


    public void DeleteCarCatalogue()
    {
        string car_id = _REQUEST["id"];
        string[] car_ids = car_id.Split(new Char[] { ',' });
        string car_id_list = "";
        string delim = "";

        if (car_ids.Length == 0)
        {
            json_response(1, "Ошибка удаления записи.");
            return;
        }

        for (int i = 0; i < car_ids.Length; i++)
        {
            if (!str_IsUInt32(car_ids[i]))
            {
                json_response(1, "Нарушение формата данных");
                return;
            }
            car_id_list += delim+car_ids[i];
            if (i == 0) delim = ",";
        }
 
        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        try
        {
            comm.CommandText =
               "BEGIN TRAN CAR_DELETE;" +
               "DELETE FROM cars WHERE car_id IN (" + car_id_list + ");" +
               "DELETE FROM price WHERE car_id IN (" + car_id_list + ");";
            Int32 result = comm.ExecuteNonQuery();
            if (result <= 0)
                json_response(1, "Ошибка удаления записи.");
            else
                json_response(0, "Ok");

            comm.CommandText = "COMMIT TRAN CAR_DELETE;";
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            comm.CommandText = "ROLLBACK TRAN CAR_DELETE;";
            comm.ExecuteNonQuery();
            json_response(1, "Ошибка удаления записи.");
        }

        conn.Close();
        conn.Dispose();
        return;
    }


    // 
    // Каталог автомобилей
    //
    public void FindInCarCatalogue()
    {
        int page = Convert.ToInt32(_REQUEST["page"]);
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

        // Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "car_id";
        sord = (sord == "desc" || sord == "DESC") ? sord : "ASC";

        switch (sidx)
        {
            case "vend_id": sidx = "cars.vend_id"; break;
            case "vend_name": sidx = "vdic.vend_name"; break;
            case "model_id": sidx = "cars.model_id"; break;
            case "model_name": sidx = "mdic.model_name"; break;
            case "color_id": sidx = "cars.color_id"; break;
            case "color_name": sidx = "cdic.color_name"; break;
            case "engine": sidx = "cars.engine"; break;
            case "price": sidx = "prc.price"; break;
            case "comment": sidx = "cars.comment"; break;
            default: sidx = "cars.car_id"; break;
        }

        // Создаем структуру для возврата
        jqGridData r = new jqGridData();

        // Фильтр
        string whereclaus = "";
        bool concat = false;
        if (search)
        {
            string scar_id = _REQUEST["car_id"];
            if (scar_id != null && scar_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.car_id=" + quote(scar_id);
                concat = true;
            }
            string svend_id = _REQUEST["vend_id"];
            if (svend_id != null && svend_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.vend_id=" + quote(svend_id);
                concat = true;
            }
            string svend_name = _REQUEST["vend_name"];
            if (svend_name != null && svend_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "vdic.vend_name LIKE " + quote("%" + svend_name + "%");
                concat = true;
            }
            string smodel_id = _REQUEST["model_id"];
            if (smodel_id != null && smodel_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.model_id=" + quote(smodel_id);
                concat = true;
            }
            string smodel_name = _REQUEST["model_name"];
            if (smodel_name != null && smodel_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "mdic.model_name LIKE " + quote("%" + smodel_name + "%");
                concat = true;
            }
            string scolor_id = _REQUEST["color_id"];
            if (scolor_id != null && scolor_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "cars.color_id=" + quote(scolor_id);
                concat = true;
            }
            string scolor_name = _REQUEST["color_name"];
            if (scolor_name != null && scolor_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "cdic.color_name LIKE " + quote("%" + scolor_name + "%");
                concat = true;
            }
            string sengine = _REQUEST["engine"];
            if (sengine != null && sengine != "")
            {
                sengine = sengine.Replace(',', '.');
                whereclaus += (concat ? " AND " : "") + "LTRIM(STR(cars.engine, 6, 2)) LIKE " + quote(sengine + "%");
                concat = true;
            }
            string lprice = _REQUEST["lprice"];
            if (lprice != null && lprice != "")
            {
                whereclaus += (concat ? " AND " : "") + "prc.price >= " + quote(lprice);
                concat = true;
            }
            string hprice = _REQUEST["hprice"];
            if (hprice != null && hprice != "")
            {
                whereclaus += (concat ? " AND " : "") + "prc.price <= " + quote(hprice);
                concat = true;
            }
        }
        if (whereclaus != "") whereclaus = " WHERE " + whereclaus;

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            echo(json_encode(r));
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        // Получаем количество записей с учетом фильтра
        string price_joining = "";
        string s_date = _REQUEST["s_date"];
        if (s_date != null && s_date != "")
        {
            price_joining = "( SELECT car_id, setdate, price FROM price AS s1" +
                            " WHERE (setdate = (SELECT MAX(setdate) AS max_s_date FROM price AS s2" +
                            " WHERE (s2.car_id = s1.car_id) AND (s2.setdate <= " + quote(s_date) + "))) )";
        }
        else
        {
            price_joining = "( SELECT car_id, setdate, price FROM price WHERE active='1' )";
        }

        comm.CommandText = "SELECT COUNT(*) AS count FROM cars " +
            "INNER JOIN vendors AS vdic ON vdic.vend_id = cars.vend_id " +
            "INNER JOIN models AS mdic ON mdic.model_id = cars.model_id " +
            "INNER JOIN colors AS cdic ON cdic.color_id = cars.color_id " +
            "LEFT  JOIN "+price_joining+" AS prc ON prc.car_id = cars.car_id" +
            whereclaus + ";";

        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

        // Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

        // Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit * page - limit + 1;
        int end_row = start_row + limit - 1;

        if (end_row > count) end_row = count;

        //SELECT * FROM ( SELECT        ROW_NUMBER() OVER (ORDER BY color_name DESC)  AS row_num, *
        //FROM colors WHERE color_code != '00FF00' ) as sub WHERE row_num BETWEEN  2 AND 3

        string sub = "SELECT ROW_NUMBER() OVER (ORDER BY " + sidx + " " + sord + ") AS RowNum, " +
                "cars.car_id  AS car_id, cars.vend_id AS vend_id, vdic.vend_name AS vend_name, " +
                "cars.model_id AS model_id, mdic.model_name AS model_name, " +
                "cars.color_id AS color_id, cdic.color_name AS color_name, " +
                "cars.engine AS engine, prc.price AS price, cars.comment AS comment FROM cars " +
                "INNER JOIN vendors AS vdic ON vdic.vend_id=cars.vend_id " +
                "INNER JOIN models  AS mdic ON mdic.model_id=cars.model_id " +
                "INNER JOIN colors  AS cdic ON cdic.color_id=cars.color_id " +
                "LEFT  JOIN " + price_joining + " AS prc ON prc.car_id = cars.car_id" +
                whereclaus;
        /*
        SELECT * 
        FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY model_name ASC) AS RowNum, 
        cars.car_id  AS car_id, cars.vend_id AS vend_id, vdic.vend_name AS vend_name, 
        cars.model_id AS model_id, mdic.model_name AS model_name, 
        cars.color_id AS color_id, cdic.color_name AS color_name, 
        cars.engine AS engine FROM cars
        LEFT JOIN vendors AS vdic ON vdic.vend_id=cars.vend_id 
        LEFT JOIN models  AS mdic ON mdic.model_id=cars.model_id
        LEFT JOIN colors  AS cdic ON cdic.color_id=cars.color_id   
        )  as sub WHERE sub.RowNum BETWEEN 1 AND 5;       
         */

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub " +
            "WHERE sub.RowNum BETWEEN " + start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row - start_row + 1];

        int icar_id = reader.GetOrdinal("car_id");
        int ivend_id = reader.GetOrdinal("vend_id");
        int ivend_name = reader.GetOrdinal("vend_name");
        int imodel_id = reader.GetOrdinal("model_id");
        int imodel_name = reader.GetOrdinal("model_name");
        int icolor_id = reader.GetOrdinal("color_id");
        int icolor_name = reader.GetOrdinal("color_name");
        int iengine = reader.GetOrdinal("engine");
        int iprice = reader.GetOrdinal("price");
        int icomment = reader.GetOrdinal("comment");

        int i = 0;
        while (reader.Read())
        {
            int car_id = reader.GetInt32(icar_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = car_id;
            r.rows[i].cell = new string[10];
            r.rows[i].cell[0] = car_id.ToString();
            r.rows[i].cell[1] = reader.GetInt32(ivend_id).ToString();
            r.rows[i].cell[2] = reader.GetString(ivend_name);
            r.rows[i].cell[3] = reader.GetInt32(imodel_id).ToString();
            r.rows[i].cell[4] = reader.GetString(imodel_name);
            r.rows[i].cell[5] = reader.GetInt32(icolor_id).ToString();
            r.rows[i].cell[6] = reader.GetString(icolor_name);
            r.rows[i].cell[7] = reader.GetFloat(iengine).ToString();

            if (reader.IsDBNull(iprice))
                r.rows[i].cell[8] = "н/д";
            else
                r.rows[i].cell[8] = reader.GetFloat(iprice).ToString();

            r.rows[i].cell[9] = reader.GetString(icomment);

            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(json_encode(r));
    }


    // 
    // Цены
    //
    public void GetCarPrice()
    {
        int page  = Convert.ToInt32(_REQUEST["page"]);
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

        // Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "car_id";
        sord = (sord == "desc" || sord == "DESC") ? sord : "ASC";

        // Создаем структуру для возврата
        jqGridData r = new jqGridData();

        // Фильтр
        string whereclaus = "";
        bool concat = false;

        string scar_id = _REQUEST["fcar_id"];
        if (scar_id != null && scar_id != "")
        {
            whereclaus += (concat ? " AND " : "") + "car_id=" + quote(scar_id);
            concat = true;
        }

        if (search)
        {
            string ssetdate = _REQUEST["setdate"];
            if (ssetdate != null && ssetdate != "")
            {
                whereclaus += (concat ? " AND " : "") + "setdate LIKE " + quote("%" + ssetdate + "%");
                concat = true;
            }
            string sprice = _REQUEST["price"];
            if (sprice != null && sprice != "")
            {
                sprice = sprice.Replace(',', '.');
                whereclaus += (concat ? " AND " : "") + "LTRIM(STR(price.price, 10, 2)) LIKE " + quote(sprice + "%");
                concat = true;
            }
            string sactive = _REQUEST["active"];
            if (sactive == "1" || sactive == "0")
            {
                whereclaus += (concat ? " AND " : "") + "active=" + quote(sactive);
                concat = true;
            }

        }
        if (whereclaus != "") whereclaus = " WHERE " + whereclaus;

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM price" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

        // Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

        // Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit * page - limit + 1;
        int end_row = start_row + limit - 1;

        if (end_row > count) end_row = count;

        string order = "ORDER BY " + sidx + " " + sord;
        string sub = "SELECT ROW_NUMBER() OVER (" + order + ") AS RowNum, * FROM price" + whereclaus;

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub WHERE sub.RowNum BETWEEN " +
                        start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row - start_row + 1];

        int iprice_id = reader.GetOrdinal("price_id");
        int icar_id = reader.GetOrdinal("car_id");
        int isetdate = reader.GetOrdinal("setdate");
        int iprice   = reader.GetOrdinal("price");
        int iactive  = reader.GetOrdinal("active");

        int i = 0;
        while (reader.Read())
        {
            int price_id = reader.GetInt32(iprice_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = price_id;
            r.rows[i].cell = new string[5];
            r.rows[i].cell[0] = price_id.ToString();
            r.rows[i].cell[1] = reader.GetInt32(iprice_id).ToString();
            r.rows[i].cell[2] = reader.GetDateTime(isetdate).ToShortDateString();
            r.rows[i].cell[3] = reader.GetFloat(iprice).ToString();
            r.rows[i].cell[4] = reader.GetString(iactive);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(json_encode(r));
    }

    public void AddToCarPrice()
    {
        string price_id = _REQUEST["id"];
        string car_id = _REQUEST["fcar_id"];
        string setdate = _REQUEST["setdate"];
        string price = _REQUEST["price"].Replace(',', '.');
        string active = _REQUEST["active"];

        if (price_id != "_empty" || !str_IsUInt32(car_id) || setdate == null || setdate.Length == 0 || 
            !str_IsReal(price) || ( active != "0" && active != "1" ))
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем существование
            comm.CommandText = "SELECT COUNT(*) FROM price WHERE setdate=" + quote(setdate) + 
                    " AND car_id="+quote(car_id)+";";

            result = (Int32) comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Цена по указаной дате уже установлена.");
                break;
            }

            // Сбрасываем, если нужно флаг активности
            if (active == "1")
            {
                comm.CommandText = "UPDATE price SET active='0' WHERE car_id=" + quote(car_id) + " AND active='1';";
                comm.ExecuteNonQuery();
            }

            // Добавляем
            comm.CommandText = "INSERT INTO price (car_id, setdate, price, active) VALUES (" +
                    quote(car_id) + ", " + quote(setdate) + ", " + quote(price) + ", " + quote(active) + ");";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка добавления новой записи.");
            else
                json_response(0, "Ok");

            break;
        }
        conn.Close();
        conn.Dispose();
        return;
    }

    public void EditCarPrice()
    {
        string price_id = _REQUEST["id"];
        string car_id = _REQUEST["fcar_id"];
        string setdate = _REQUEST["setdate"];
        string price = _REQUEST["price"].Replace(',', '.');
        string active = _REQUEST["active"];

        if (!str_IsUInt32(price_id) || !str_IsUInt32(car_id) || setdate == null || setdate.Length == 0 || 
            !str_IsReal(price) || (active!="1" && active!="0"))
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM price WHERE setdate=" + quote(setdate) + 
            " AND car_id = " + car_id + " AND price_id !="+quote(price_id)+";";

            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Цена по указаной дате уже установлена.");
                break;
            }

            // Сбрасываем, если нужно флаг активности
            if (active == "1")
            {
                comm.CommandText = "UPDATE price SET active='0' WHERE car_id=" + quote(car_id) + " AND active='1';";
                comm.ExecuteNonQuery();
            }

            // Изменяем
            comm.CommandText = "UPDATE price SET car_id=" + quote(car_id) + ", setdate=" + quote(setdate) + 
                ", price=" + quote(price) + ", active="+quote(active)+
                " WHERE price_id=" + quote(price_id) + ";";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка обновления записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void DeleteCarPrice()
    {
        string price_id = _REQUEST["id"];
        string[] price_ids = price_id.Split(new Char[] { ',' });
        string price_id_list = "";
        string delim = "";

        if (price_ids.Length == 0)
        {
            json_response(1, "Ошибка удаления записи.");
            return;
        }

        for (int i = 0; i < price_ids.Length; i++)
        {
            if (!str_IsUInt32(price_ids[i]))
            {
                json_response(1, "Нарушение формата данных");
                return;
            }
            price_id_list += delim + price_ids[i];
            if (i == 0) delim = ",";
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "DELETE FROM price WHERE price_id IN (" + price_id_list + ");";

        Int32 result = comm.ExecuteNonQuery();
        if (result <= 0)
            json_response(1, "Ошибка удаления записи.");
        else
            json_response(0, "Ok");

        conn.Close();
        conn.Dispose();
        return;
    }



} //End of class CallBacks
